/**
 * 完全更新basic_items表ID脚本
 * 功能：使用更智能的策略，包括临时重排、批量交换等方法，确保100%匹配
 */

const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库连接配置（硬编码）
const DB_CONFIG = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

class CompleteBasicItemsIdUpdater {
    constructor() {
        this.connection = null;
        this.jsonData = null;
        this.updateStats = {
            total: 0,
            matched: 0,
            updated: 0,
            errors: 0,
            rounds: 0
        };
        this.tempIdStart = 1000000; // 临时ID起始值
        this.currentTempId = 1000000;
    }

    // 连接数据库
    async connectDatabase() {
        try {
            console.log('🔌 正在连接数据库...');
            this.connection = await mysql.createConnection(DB_CONFIG);
            console.log('✅ 数据库连接成功');
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            throw error;
        }
    }

    // 关闭数据库连接
    async closeDatabase() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    // 加载JSON数据
    loadJsonData() {
        try {
            console.log('📄 正在加载基础物品JSON数据...');
            const jsonPath = path.join(__dirname, 'output', '基础物品_0911.json');

            if (!fs.existsSync(jsonPath)) {
                throw new Error(`JSON文件不存在: ${jsonPath}`);
            }

            const jsonContent = fs.readFileSync(jsonPath, 'utf8');
            this.jsonData = JSON.parse(jsonContent);

            console.log(`✅ JSON数据加载成功，共 ${Object.keys(this.jsonData).length} 条记录`);
        } catch (error) {
            console.error('❌ 加载JSON数据失败:', error.message);
            throw error;
        }
    }

    // 从JSON数据中提取物品名称和ID的映射
    extractNameIdMapping() {
        const nameIdMap = new Map();

        for (const [id, itemData] of Object.entries(this.jsonData)) {
            if (itemData && itemData.data && Array.isArray(itemData.data)) {
                // 查找名称字段
                const nameField = itemData.data.find(field => field.k === '名称:');
                if (nameField && nameField.v) {
                    nameIdMap.set(nameField.v.trim(), parseInt(id));
                }
            }
        }

        console.log(`📊 从JSON中提取到 ${nameIdMap.size} 个物品名称-ID映射`);
        return nameIdMap;
    }

    // 查询数据库中的basic_items表
    async getDatabaseItems() {
        try {
            console.log('🔍 正在查询数据库中的basic_items表...');
            const [rows] = await this.connection.execute(
                'SELECT id, name FROM basic_items ORDER BY id'
            );

            console.log(`✅ 数据库查询成功，共 ${rows.length} 条记录`);
            return rows;
        } catch (error) {
            console.error('❌ 查询数据库失败:', error.message);
            throw error;
        }
    }

    // 获取下一个临时ID
    getNextTempId() {
        return this.currentTempId++;
    }

    // 分析不匹配的物品，找出冲突模式
    analyzeConflicts(nameIdMap, dbItems) {
        const conflicts = [];
        const mismatches = [];

        for (const dbItem of dbItems) {
            const dbName = dbItem.name.trim();
            const dbId = dbItem.id;

            if (nameIdMap.has(dbName)) {
                const jsonId = nameIdMap.get(dbName);
                if (dbId !== jsonId) {
                    mismatches.push({ name: dbName, dbId, jsonId });

                    // 检查是否存在循环冲突
                    const conflictItem = dbItems.find(item => item.id === jsonId);
                    if (conflictItem && nameIdMap.has(conflictItem.name)) {
                        const conflictJsonId = nameIdMap.get(conflictItem.name);
                        if (conflictJsonId === dbId) {
                            conflicts.push({
                                item1: { name: dbName, dbId, jsonId },
                                item2: { name: conflictItem.name, dbId: jsonId, jsonId: conflictJsonId }
                            });
                        }
                    }
                }
            }
        }

        return { conflicts, mismatches };
    }

    // 处理循环冲突（A想占用B的ID，B想占用A的ID）
    async resolveCircularConflicts(conflicts) {
        console.log(`\n🔄 发现 ${conflicts.length} 个循环冲突，开始解决...`);

        for (const conflict of conflicts) {
            const { item1, item2 } = conflict;
            console.log(`\n处理循环冲突:`);
            console.log(`  "${item1.name}" (DB:${item1.dbId} → JSON:${item1.jsonId})`);
            console.log(`  "${item2.name}" (DB:${item2.dbId} → JSON:${item2.jsonId})`);

            try {
                await this.connection.query('START TRANSACTION');

                // 使用临时ID解决循环冲突
                const tempId1 = this.getNextTempId();
                const tempId2 = this.getNextTempId();

                // 1. 将两个物品都移动到临时ID
                await this.connection.execute(
                    'UPDATE basic_items SET id = ? WHERE id = ?',
                    [tempId1, item1.dbId]
                );
                await this.connection.execute(
                    'UPDATE basic_items SET id = ? WHERE id = ?',
                    [tempId2, item2.dbId]
                );

                // 2. 交换到目标ID
                await this.connection.execute(
                    'UPDATE basic_items SET id = ? WHERE id = ?',
                    [item1.jsonId, tempId1]
                );
                await this.connection.execute(
                    'UPDATE basic_items SET id = ? WHERE id = ?',
                    [item2.jsonId, tempId2]
                );

                await this.connection.query('COMMIT');

                console.log(`✅ 循环冲突解决成功`);
                this.updateStats.updated += 2;

            } catch (error) {
                await this.connection.query('ROLLBACK');
                console.error(`❌ 解决循环冲突失败:`, error.message);
                this.updateStats.errors += 2;
            }
        }
    }

    // 处理剩余的不匹配物品
    async resolveRemainingMismatches(mismatches) {
        console.log(`\n🔧 处理剩余 ${mismatches.length} 个不匹配物品...`);

        // 按ID排序，优先处理小ID
        mismatches.sort((a, b) => a.jsonId - b.jsonId);

        for (const mismatch of mismatches) {
            const { name, dbId, jsonId } = mismatch;

            try {
                // 检查目标ID是否已被占用
                const [existingRows] = await this.connection.execute(
                    'SELECT id, name FROM basic_items WHERE id = ?',
                    [jsonId]
                );

                if (existingRows.length > 0) {
                    const existingItem = existingRows[0];
                    if (existingItem.name !== name) {
                        console.log(`⚠️  ID ${jsonId} 被 "${existingItem.name}" 占用，跳过 "${name}"`);
                        continue;
                    }
                }

                // 如果目标ID空闲，直接更新
                if (existingRows.length === 0) {
                    await this.connection.query('START TRANSACTION');

                    const tempId = this.getNextTempId();

                    // 移动到临时ID
                    await this.connection.execute(
                        'UPDATE basic_items SET id = ? WHERE id = ?',
                        [tempId, dbId]
                    );

                    // 移动到目标ID
                    await this.connection.execute(
                        'UPDATE basic_items SET id = ? WHERE id = ?',
                        [jsonId, tempId]
                    );

                    await this.connection.query('COMMIT');

                    console.log(`✅ 更新成功: "${name}" ID ${dbId} → ${jsonId}`);
                    this.updateStats.updated++;
                }

            } catch (error) {
                console.error(`❌ 更新失败: "${name}" ID ${dbId} → ${jsonId}`, error.message);
                this.updateStats.errors++;
            }
        }
    }

    // 执行完全更新流程
    async performCompleteUpdate() {
        try {
            // 提取JSON中的名称-ID映射
            const nameIdMap = this.extractNameIdMapping();

            // 查询数据库中的物品
            let dbItems = await this.getDatabaseItems();
            this.updateStats.total = dbItems.length;

            console.log('\n🚀 开始完全更新ID...');
            console.log('=' * 60);

            let maxRounds = 20; // 增加最大轮数
            let lastUpdatedCount = 0;
            let stableRounds = 0;

            for (let round = 0; round < maxRounds; round++) {
                console.log(`\n🔄 开始第 ${round + 1} 轮更新...`);

                // 分析冲突
                const { conflicts, mismatches } = this.analyzeConflicts(nameIdMap, dbItems);

                console.log(`📊 第 ${round + 1} 轮分析结果:`);
                console.log(`   循环冲突: ${conflicts.length} 个`);
                console.log(`   不匹配物品: ${mismatches.length} 个`);

                if (conflicts.length === 0 && mismatches.length === 0) {
                    console.log(`\n✅ 所有物品ID已匹配，更新完成！`);
                    break;
                }

                // 处理循环冲突
                if (conflicts.length > 0) {
                    await this.resolveCircularConflicts(conflicts);
                }

                // 处理剩余不匹配
                if (mismatches.length > 0) {
                    await this.resolveRemainingMismatches(mismatches);
                }

                // 重新查询数据库
                dbItems = await this.getDatabaseItems();

                // 检查是否稳定
                const currentMismatches = this.analyzeConflicts(nameIdMap, dbItems).mismatches.length;
                if (currentMismatches === lastUpdatedCount) {
                    stableRounds++;
                    if (stableRounds >= 3) {
                        console.log(`\n⚠️  连续3轮无变化，可能存在无法解决的冲突`);
                        break;
                    }
                } else {
                    stableRounds = 0;
                }

                lastUpdatedCount = currentMismatches;
                this.updateStats.rounds++;

                // 短暂延迟
                await this.delay(200);
            }

            console.log('\n' + '=' * 60);
            console.log('📊 完全更新统计:');
            console.log(`   总处理数量: ${this.updateStats.total}`);
            console.log(`   执行轮数: ${this.updateStats.rounds}`);
            console.log(`   累计更新数量: ${this.updateStats.updated}`);
            console.log(`   累计错误数量: ${this.updateStats.errors}`);

        } catch (error) {
            console.error('❌ 完全更新过程中发生错误:', error.message);
            throw error;
        }
    }

    // 验证更新结果
    async verifyUpdate() {
        try {
            console.log('\n🔍 正在验证更新结果...');

            const nameIdMap = this.extractNameIdMapping();
            const dbItems = await this.getDatabaseItems();

            let matchCount = 0;
            let mismatchCount = 0;
            const mismatches = [];

            for (const dbItem of dbItems) {
                const dbName = dbItem.name.trim();
                const dbId = dbItem.id;

                if (nameIdMap.has(dbName)) {
                    const jsonId = nameIdMap.get(dbName);
                    if (dbId === jsonId) {
                        matchCount++;
                    } else {
                        mismatchCount++;
                        mismatches.push({ name: dbName, dbId, jsonId });
                    }
                }
            }

            console.log(`\n📊 最终验证结果:`);
            console.log(`   匹配数量: ${matchCount}`);
            console.log(`   不匹配数量: ${mismatchCount}`);
            console.log(`   匹配率: ${((matchCount / (matchCount + mismatchCount)) * 100).toFixed(2)}%`);

            if (mismatchCount > 0) {
                console.log(`\n❌ 剩余不匹配的物品:`);
                mismatches.forEach(m => {
                    console.log(`   "${m.name}" 数据库ID:${m.dbId}, JSON ID:${m.jsonId}`);
                });
            } else {
                console.log(`\n🎉 所有物品ID已完全匹配！`);
            }

        } catch (error) {
            console.error('❌ 验证过程中发生错误:', error.message);
        }
    }

    // 延迟工具方法
    delay(ms) {
        return new Promise(resolve => setTimeout(resolve, ms));
    }

    // 运行完整的完全更新流程
    async runCompleteUpdate() {
        console.log('🚀 开始完全更新basic_items表ID...\n');

        try {
            // 1. 连接数据库
            await this.connectDatabase();

            // 2. 加载JSON数据
            this.loadJsonData();

            // 3. 执行完全ID更新
            await this.performCompleteUpdate();

            // 4. 验证更新结果
            await this.verifyUpdate();

            console.log('\n🎉 完全ID更新流程完成！');

        } catch (error) {
            console.error('💥 完全更新流程失败:', error.message);
            throw error;
        } finally {
            // 5. 关闭数据库连接
            await this.closeDatabase();
        }
    }
}

// 运行完全更新
async function main() {
    console.log('🎯 basic_items表完全ID更新工具');
    console.log('   目标：使用智能策略确保100%匹配\n');

    const updater = new CompleteBasicItemsIdUpdater();
    await updater.runCompleteUpdate();
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch((error) => {
        console.error('💥 脚本执行失败:', error.message);
        process.exit(1);
    });
}

module.exports = CompleteBasicItemsIdUpdater;
